/**
 * Created by xc on 2017/03/10.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var ItbUserModel = require('./ItbUserModel');
var async  = require( 'async'); // 同步控制


// Schema 结构
var ItbCouponModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '优惠券id',
  //`owner` bigint(20) NOT NULL DEFAULT '0' COMMENT '优惠券拥有方：合作方表id',
  //`lot_no` varchar(20) NOT NULL DEFAULT '0' COMMENT '批次号：年月日时分秒',
  //`type` int(3) NOT NULL DEFAULT '0' COMMENT '类型:10直接抵扣,20满减券,30折扣券',
  //`c_code` varchar(20) NOT NULL COMMENT '优惠码',
  //`limit_type` bigint(20) NOT NULL DEFAULT '0' COMMENT '限制范围: 暂定为tg_type分类',
  //`discount` bigint(20) NOT NULL DEFAULT '0' COMMENT '优惠额度:金额单位分(折扣券时为1~99)',
  //`min_order_fee` bigint(20) NOT NULL DEFAULT '0' COMMENT '最小订单金额',
  //`issue_time` datetime NOT NULL COMMENT '发放时间',
  //`expiry_time` datetime DEFAULT NULL COMMENT '有效截止时间',
  //`used_time` datetime DEFAULT NULL COMMENT '使用时间',
  //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  //`trade_type` int(3) DEFAULT NULL COMMENT '订单种别: 10商品订单，30购买模版',
  //`trade_obj_id` bigint(20) DEFAULT NULL COMMENT '订单id（商品订单order_id或购买模版buy_id）',
  //`state` int(3) NOT NULL DEFAULT '10' COMMENT '状态:10新建,20已兑换(绑定到用户),30已绑定到订单,40已使用',
  //`desc` varchar(200) DEFAULT '' COMMENT '备注',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) DEFAULT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbCouponModel.prototype = new ItbBaseModel();

// 取得表名
ItbCouponModel.prototype.getTableName = function(){
    return 'itb_coupon';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbCouponModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'c_id='+this.c_id;
};

/**
 * 获取订单优惠券相关的 sql
 * @param bForCnt: true [仅用于取count]; false [取数据]
 * @param userId: 用户id
 * @param valid: 0 不符合, 1 符合
 * @param orderFee: 订单费用
 * @param pageNum: 取数据时分页页码
 * @param pageSize: 取数据时, 分页大小
 * @return 对应的 sql
 *
 * add: 2017-03-10 by: wangJun
 */
function sql_getCouponInfoByUser(bForCnt, userId, valid, orderFee, pageNum, pageSize) {

    var sql = '';
    if(bForCnt) {
        sql += 'SELECT count(*) AS count';
    } else {
        sql += ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, t.c_code, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type ';
    }

    sql += ' FROM itb_coupon t '+
        ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
        ' WHERE t.user_id='+userId+
        ' AND t.del_flg=0 ';
    if(valid==1) {
        sql += ' AND (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) AND t.state=20 ';
        if(Number(orderFee||'0')>0) {
            sql += ' AND (t.type!=20 OR (t.type=20 AND t.min_order_fee<='+orderFee+'))';
        }
    } else {
        sql += ' AND ( sysdate()>t.expiry_time OR t.state IN (30,40) ';
        if(Number(orderFee||'0')>0) {
            sql += ' OR (t.state=20 AND t.type=20 AND t.min_order_fee>'+orderFee+')';
        }
        sql += ' ) ';
    }
    if(!bForCnt) {
        sql += ' ORDER BY t.expiry_time ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
    }
    return sql;
}
// 根据用户id搜索优惠券信息
// 参数: userId 兑换码
// 参数: valid 是否有效
// 参数: orderFee 订单金额(大于0时有效,用于判断满减券)
ItbCouponModel.prototype.getCouponInfoByUser = function(userId, valid, orderFee, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUser Err:'+err);
        }
        // Use the connection
        //var sql = ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
        //    ' t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, t.c_code, '+
        //    ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
        //    ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
        //    ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
        //    ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
        //    ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
        //    ' ifnull(p.top_parent,0) AS top_limit_type '+
        //    ' FROM itb_coupon t '+
        //    ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
        //    ' WHERE t.user_id='+userId+
        //    ' AND t.del_flg=0 ';
        //if(valid==1) {
        //    sql += ' AND (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) AND t.state=20 ';
        //    if(Number(orderFee||'0')>0) {
        //        sql += ' AND (t.type!=20 OR (t.type=20 AND t.min_order_fee<='+orderFee+'))';
        //    }
        //} else {
        //    sql += ' AND ( sysdate()>t.expiry_time OR t.state IN (30,40) ';
        //    if(Number(orderFee||'0')>0) {
        //        sql += ' OR (t.state=20 AND t.type=20 AND t.min_order_fee>'+orderFee+')';
        //    }
        //    sql += ' ) ';
        //}
        //sql += ' ORDER BY t.expiry_time ';

        //var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        //if(typeof skipCnt!='undefined' && skipCnt != null
        //    && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
        //    sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        //}
        var  sql = sql_getCouponInfoByUser(false, userId, valid, orderFee, pageNum, pageSize);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};
// todo 获取符合订单条件的订单个数 begin
ItbCouponModel.prototype.getCouponInfoByUserCnt = function(userId, valid, orderFee, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUserCnt Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getCouponInfoByUser(true, userId, valid, orderFee, null, null);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};
// todo 获取符合订单条件的订单个数 end

// 检查指定手机号是否抽过奖(合伙人抽奖用)
// 参数: mobile 手机号
// 参数: chkDtStart[可选]  'YYYY-MM-DD hh:mm:ss'
// 参数: chkDtEnd[可选]  'YYYY-MM-DD hh:mm:ss'
ItbCouponModel.prototype.chkCouponByMobile = function(mobile, chkDtStart, chkDtEnd, callback) {
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " select t.c_id " +
            " FROM itb_coupon t " +
            " WHERE t.del_flg=0 AND t.mobile= " + mobile+
            " AND t.channel_u IS NOT NULL AND t.channel_u>0 ";
        if(chkDtStart && chkDtStart.length>0) {
            sql += " AND t.draw_time> '" + chkDtStart + "'";
        }
        if(chkDtEnd && chkDtEnd.length>0) {
            sql += " AND t.draw_time< '" + chkDtEnd + "'";
        }

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            var ret = false;
            if(result && result.length>0) {
                ret = true;
            }
            callback(error, ret);
        });
    });
};

// 检查指定用户是否抽过奖
// 参数:  userId 手机号
// 参数: chkDtStart[可选]  'YYYY-MM-DD hh:mm:ss'
// 参数: chkDtEnd[可选]  'YYYY-MM-DD hh:mm:ss'
ItbCouponModel.prototype.chkCouponByUserId = function(userId, chkDtStart, chkDtEnd, callback) {
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " select t.c_id " +
            " FROM itb_coupon t " +
            " WHERE t.del_flg=0 AND t.user_id= " + userId+
            " AND (t.channel_u IS NULL OR t.channel_u<=0) ";
        if(chkDtStart && chkDtStart.length>0) {
            sql += " AND t.draw_time> '" + chkDtStart + "'";
        }
        if(chkDtEnd && chkDtEnd.length>0) {
            sql += " AND t.draw_time< '" + chkDtEnd + "'";
        }

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            var ret = false;
            if(result && result.length>0) {
                ret = true;
            }
            callback(error, ret);
        });
    });
};
/**
 * 兑换优惠券
 * 参数 userId
 * 参数 cId/cCd: 优惠券id/优惠券码(至少一个不为空)
 */
ItbCouponModel.prototype.exchgCoupon = function(userId, cId, cCd, callback) {
    var THIS = this;

    if(!cId && !cCd) {
        return callback('参数错误',null);
    }

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE itb_coupon t "+
            " SET t.update_time=sysdate() "+
            "     ,t.exchg_time=sysdate() "+
            "     ,t.user_id= "+userId+
            "     ,t.state=20 "+// 绑定到用户
            " WHERE t.del_flg=0 AND t.state IN (10,15) ";

        if(cId && Number(cId||'0')>0) {
            sql += " AND t.c_id= "+cId;
        }
        if(cCd && cCd.length>0) {
            sql += " AND t.c_code= "+cCd;
        }

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[ItbCouponModel.js] [exchgCoupon] Upd Err: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

// 根据兑换码搜索优惠券信息
// 参数: c_code 兑换码
ItbCouponModel.prototype.getCouponInfoByCode = function(c_code, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByCode Err:'+err);
        }
        // Use the connection
        var sql = ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type '+
            ' FROM itb_coupon t '+
            ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
            ' WHERE t.c_code="'+c_code+'"'+
            ' AND t.del_flg=0';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};

// 绑定(商品)订单
// 参数 discount: 折扣金额
// 参数 orderFee: 折扣后订单金额
ItbCouponModel.prototype.bindOrder = function(couponId, discount, orderId, orderFee, callback) {
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.bindOrder Err:'+err);
        }

        var trans = connection.beginTransaction(function(error){
            if (error) {
                // And done with the connection.
                connection.release();
                return callback(error,null)
            }else{
                var asynFuncs = [
                    function(callback1){
                        // 更新订单
                        var sql = " UPDATE itb_trade_order t "+
                            " SET t.total_fee="+Number(orderFee||'0')+
                            " 	  ,t.coupon_id="+couponId+
                            " 	  ,t.coupon_fee="+Number(discount||'0')+
                            " 	  ,t.update_time=sysdate() "+
                            " WHERE t.order_id="+orderId;

                        connection.query(sql,function(error,info){
                            callback1(error,info);
                        });
                    },
                    function(callback2){
                        // 更新优惠券
                        var sql = " UPDATE itb_coupon t"+
                            " SET t.trade_type=10 "+// 商品订单
                            "     ,t.trade_obj_id="+orderId+
                            "     ,t.state=30 "+// 绑定到订单
                            "     ,t.update_time=sysdate() "+
                            " WHERE t.c_id="+couponId;

                        connection.query(sql,function(error,info){
                            callback2(error,info);
                        });
                    }
                ];
                async.series(asynFuncs,function(error,results){
                    if (error) {
                        connection.rollback();

                        // And done with the connection.
                        connection.release();
                        return callback(error, null)

                    } else {
                        // 没有错误 提交事务
                        connection.commit(function(error, info) {
                            // And done with the connection.
                            connection.release();

                            if(error){
                                return callback(error, null);
                            }else{
                                return callback(null, null);
                            }
                        });
                    }
                });
            }
        });
    });
};

// 解绑(商品)订单
// 参数 orderFee: 解绑后订单金额
ItbCouponModel.prototype.unbindOrder = function(couponId, orderId, orderFee, callback) {
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.unbindOrder Err:'+err);
        }

        var trans = connection.beginTransaction(function(error){
            if (error) {
                // And done with the connection.
                connection.release();
                return callback(error,null)
            }else{
                var asynFuncs = [
                    function(callback1){
                        // 更新订单
                        var sql = " UPDATE itb_trade_order t "+
                            " SET t.total_fee="+Number(orderFee||'0')+
                            " 	  ,t.coupon_id=null "+
                            " 	  ,t.coupon_fee=0 "+
                            " 	  ,t.update_time=sysdate() "+
                            " WHERE t.order_id="+orderId;

                        connection.query(sql,function(error,info){
                            callback1(error,info);
                        });
                    },
                    function(callback2){
                        // 更新优惠券
                        var sql = " UPDATE itb_coupon t"+
                            " SET t.trade_type=null "+
                            "     ,t.trade_obj_id=null "+
                            "     ,t.state=20 "+// 绑定到用户
                            "     ,t.update_time=sysdate() "+
                            " WHERE t.c_id="+couponId;

                        connection.query(sql,function(error,info){
                            callback2(error,info);
                        });
                    }
                ];
                async.series(asynFuncs,function(error,results){
                    if (error) {
                        connection.rollback();

                        // And done with the connection.
                        connection.release();
                        return callback(error, null)

                    } else {
                        // 没有错误 提交事务
                        connection.commit(function(error, info) {
                            // And done with the connection.
                            connection.release();

                            if(error){
                                return callback(error, null);
                            }else{
                                return callback(null, null);
                            }
                        });
                    }
                });
            }
        });
    });
};


// 释放[取消的(商品)订单]所绑定的优惠券: 仅更新优惠券，不更新订单
ItbCouponModel.prototype.unbindCanceledOrderOnly = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE itb_coupon t, itb_trade_order o"+
            " SET t.trade_type=null "+
            "     ,t.trade_obj_id=null "+
            "     ,t.state=20 "+// 绑定到用户
            "     ,t.update_time=sysdate() "+
            " WHERE o.order_id="+orderId+" AND o.coupon_id is not null AND o.state=50 AND o.del_flg=0 "+// 取消的订单
            " AND t.c_id=o.coupon_id AND t.state=30 AND t.del_flg=0 ";// 当前已绑定到订单的优惠券

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[ItbCouponModel.js] [unbindCanceledOrderOnly] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

// 对于已支付成功的订单，更新关联的优惠券状态
ItbCouponModel.prototype.updStateByPaidOrder = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE itb_coupon t, itb_trade_order o"+
            " SET t.used_time=sysdate() "+
            "     ,t.state=40 "+// 已使用
            "     ,t.update_time=sysdate() "+
            " WHERE o.order_id="+orderId+" AND o.coupon_id is not null AND o.state in (11,20,30,40) AND o.del_flg=0 "+// 支付成功的订单
            " AND t.c_id=o.coupon_id AND t.state=30 AND t.del_flg=0 ";// 当前已绑定到订单的优惠券

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[ItbCouponModel.js] [updStateByPaidOrder] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

// 根据支付成功的(商品)订单更新优惠券状态（非严格判断）
// 例如：用于将[取消]的订单再更新为[支付成功]的场合
ItbCouponModel.prototype.updStateByPaidOrder2 = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE itb_coupon t, itb_trade_order o"+
            " SET t.used_time=sysdate() "+
            "     ,t.trade_type=10 "+// 商品订单
            "     ,t.trade_obj_id="+orderId+
            "     ,t.state=40 "+// 已使用
            "     ,t.update_time=sysdate() "+
            " WHERE o.order_id="+orderId+" AND o.coupon_id is not null AND o.del_flg=0 "+
            " AND t.c_id=o.coupon_id AND t.user_id=o.user_id AND t.state in (20,30) AND t.del_flg=0 ";

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[ItbCouponModel.js] [updStateByPaidOrder2] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

// 兑换优惠券（未注册则标记成领用状态，已注册则标记成已兑换（并更新user_id））
// codes: 逗号拼接优惠码
ItbCouponModel.prototype.bindUserFromPartner = function(partnerUID, mobile, codes, callback){
    var THIS = this;

    // 检查手机号对应用户
    var ent = new ItbUserModel({
        mobile: mobile,
        del_flg: 0
    });
    ent.findFromDB(function(err, ret){
        if(err) {
            return callback(err, null);
        }

        var userId = null;
        if(ret && ret.length>0) {
            userId = ret[0].user_id;
        }

        pool.pool.getConnection(function(err, connection) {

            var sql = " UPDATE itb_coupon t "+
                " SET t.update_time=sysdate() "+
                "     ,t.channel_u="+partnerUID+
                "     ,t.mobile="+mobile;
            if(userId && userId>0) {
                sql += ' ,t.draw_time=sysdate(), t.exchg_time=sysdate(), t.state=20, t.user_id='+userId;// 兑换
            } else {
                sql += ' ,t.draw_time=sysdate(), t.state=15 ';// 占用
            }

            sql += ' WHERE t.del_flg=0 AND t.state=10 AND t.use IN (30,31,32) '+
                ' AND t.c_code IN ('+codes+')';

            console.log(sql);
            connection.query(sql, function (error, result) {
                if (error) {
                    console.log('[ItbCouponModel.js] [bindUserFromPartner] Upd Err: ' + error);
                }
                connection.release();

                callback(error, result);
            });
        });
    });
};

// 获取抽奖用优惠码
// 参数 use: 用途
// 参数 maxCnt: 获取最大个数(默认100)
ItbCouponModel.prototype.getCouponForLuckyDraw = function(use, maxCnt, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponForLuckyDraw Err:'+err);
        }
        // Use the connection
        var sql = ' SELECT t.c_id, t.c_code, t.owner, t.lot_no, t.type, t.use, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type '+
            ' FROM itb_coupon t '+
            ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
            ' WHERE (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) '+// 未过期
            ' AND t.state=10 AND t.del_flg=0 AND t.use='+use;// 新建的,用于抽奖的
        sql += ' limit ' + Number(maxCnt||'100');

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};

// todo 新增接口, 待审核 begin
/**
 * 根据商品订单 id 获取该订单对应的优惠券信息
 * 参数: tradeOrderId 商品订单 id
 * 参数: callback 回调
 * */
ItbCouponModel.prototype.getCouponInfoByTradeOrderId = function(tradeOrderId, callback){
    pool.pool.getConnection(function(err, connection){
        if(err){
            console.log('ItbCouponModel.getCouponInfoByTradeOrderId errorL' + err);
        }
        var sql =
            "SELECT c.c_id AS id, " +
            "       c.c_id AS c_id, " +
            "       c.c_code AS code, " +
            "       c.c_code AS c_code, " +
            "       c.limit_type AS limit_type, " +
            "       c.limit_type AS top_limit_type, " +
            "       c.type AS type, " +
            "       c.state AS state, " +
            "       c.discount AS discount, " +
            "       case when sysdate()>c.expiry_time then 1 else 0 end AS expiry_flg, "+
            "       CASE " +
            "           WHEN c.type = 10 THEN '抵扣券' " +
            "           WHEN c.type = 20 THEN '满减券' " +
            "           WHEN c.type = 30 THEN '折扣券' " +
            "           ELSE '' " +
            "       END AS type_desc, " +
            "       CASE " +
            "           WHEN c.type = 30 THEN concat(ROUND(c.discount/100,1), '折') " +
            "           ELSE concat(ROUND(c.discount/100,1), '元') " +
            "       END AS discount_desc, " +
            "       c.trade_type AS trade_type, " +
            '       ifnull(DATE_FORMAT(c.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            "       c.desc AS coupon_desc, " +
            "       '优惠券已绑定订单' AS reason " +
            "FROM itb_trade_order o, " +
            "     itb_coupon c " +
            "WHERE o.order_id= " + tradeOrderId +
            "  AND o.coupon_id = c.c_id ";
        console.log(sql);
        connection.query(sql, function(error, result){
            connection.release();

            if(result && result.length >0) {
                return callback(null, result[0]);
            }
            callback(error, null);
        });
    });
};
// todo 新增接口, 待审核 end

ItbCouponModel.prototype.getCouponInfoById = function(cId, callback){
    pool.pool.getConnection(function(err, connection){
        if(err){
            console.log('ItbCouponModel.getCouponInfoById error' + err);
        }

        var sql = ' SELECT t.c_id, t.c_code, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type '+
            ' FROM itb_coupon t '+
            ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
            ' WHERE t.c_id in ('+cId+')'+
            ' AND t.del_flg=0';

        console.log(sql);
        connection.query(sql, function(error, result){
            connection.release();

            if(result && result.length >0) {
                return callback(null, result[0]);
            }
            callback(error, null);
        });
    });
};


// 兑换多张优惠券
// 参数: codes, 多个 code 的 , 拼接, 如 "YYYYYY", "TTTTTT"
ItbCouponModel.prototype.getCouponInfoByCodes = function(codes, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByCode Err:'+err);
        }
        // Use the connection
        var sql = ' SELECT t.c_id, t.c_code, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type '+
            ' FROM itb_coupon t '+
            ' LEFT JOIN tg_type p ON p.del_flg=0 AND p.type_id=t.limit_type '+
            ' WHERE t.c_code in ('+codes+')'+
            ' AND t.del_flg=0';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};


// vip充值用优惠券
function sql_getVipCouponInfo(bForCnt, userId, valid, pageNum, pageSize){
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT count(*) AS count ';
    } else {
        sql += ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, t.c_code, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type ';
    }

    sql += ' FROM itb_coupon t, tg_type p '+
        ' WHERE t.user_id='+userId+
        ' AND t.del_flg=0 '+
        ' AND p.type_id=t.limit_type AND p.del_flg=0 ';
    if(valid) {
        // TODO 暂定vip充值优惠券只有［抵扣券］，未考虑［满减券］
        sql += ' AND p.top_parent = 9 '+// 虚拟商品就用
            ' AND (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) '+
            ' AND t.state=20 ';
    } else {
        sql += ' AND ( sysdate()>t.expiry_time OR t.state IN (30,40,50) OR p.top_parent!=9 )'
    }

    if(!bForCnt) {
        sql += ' ORDER BY t.expiry_time ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
    }

    return sql;
};

// 根据用户id搜索vip充值用优惠券
// 参数: userId
// 参数: valid 是否可用
ItbCouponModel.prototype.getVipCouponInfo = function(userId, valid, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getVipCouponInfo Err:'+err);
        }
        var  sql = sql_getVipCouponInfo(false, userId, valid, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};

ItbCouponModel.prototype.getVipCouponInfoCnt = function(userId, valid, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getVipCouponInfoCnt Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getVipCouponInfo(true, userId, valid, null, null);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};


//-------------------------------------------------------------------------------------
// 秒杀用优惠券
// TODO: 暂定仅LOGO券可用
function sql_getSeckillCouponInfo(bForCnt, userId, actItemId, valid, pageNum, pageSize){

    // 子sql：判断是否为LOGO商品
    var sql1 = ' SELECT CASE WHEN tp.top_parent=1010 THEN 1 ELSE 0 END AS isLogo '+
        ' FROM itb_activity_r ar, itb_items it, tg_type tp '+
        ' WHERE ar.del_flg=0 AND ar.r_id='+actItemId+
        ' AND it.del_flg=0 AND it.item_id=ar.item_id '+
        ' AND tp.del_flg=0 AND tp.type_id=it.type_id ';

    // 总sql
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT count(*) AS count ';
    } else {
        sql += ' SELECT y.c_id, y.owner, y.lot_no, y.type, y.limit_type, y.discount, y.min_order_fee, '+
            ' y.mobile, y.user_id, y.trade_type, y.trade_obj_id, y.state, y.desc, y.c_code, '+
            ' DATE_FORMAT(y.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(y.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(y.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case y.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>y.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(y.top_limit_type,0) AS top_limit_type ';
    }

    sql += ' FROM ( ';
    sql += ' SELECT t.*, p.top_parent AS top_limit_type, ifnull(x.isLogo,0) AS isLogo ';
    sql += ' FROM itb_coupon t ';
    sql += ' INNER JOIN tg_type p ON p.type_id=t.limit_type AND p.del_flg=0 ';
    sql += ' LEFT JOIN ( '+sql1+' ) x ON 1=1 ';
    sql += ' WHERE t.del_flg=0 AND t.user_id='+userId;
    sql += ' ) y ';

    if(valid) {
        sql += ' WHERE y.isLogo=1 '+
            ' AND y.top_limit_type = 1010 '+
            ' AND (sysdate()<=y.expiry_time OR y.expiry_time IS NULL) '+
            ' AND y.state=20 ';
    } else {
        sql += ' WHERE y.isLogo=0 '+
            ' OR y.top_limit_type != 1010 '+
            ' OR sysdate()>y.expiry_time '+
            ' OR y.state IN (30,40,50) ';
    }

    if(!bForCnt) {
        sql += ' ORDER BY y.expiry_time ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
    }

    return sql;
};

// 搜索秒杀商品可用／不可用优惠券
// 参数: valid 是否可用
ItbCouponModel.prototype.getSeckillCouponInfo = function(userId, actItemId, valid, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getSeckillCouponInfo Err:'+err);
        }
        var  sql = sql_getSeckillCouponInfo(false, userId, actItemId, valid, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};
ItbCouponModel.prototype.getSeckillCouponInfoCnt = function(userId, actItemId, valid, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getSeckillCouponInfoCnt Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getSeckillCouponInfo(true, userId, actItemId, valid, null, null);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};
//-------------------------------------------------------------------------------------


// 印品优惠券
function sql_getOrderCouponInfo(bForCnt, userId, valid, orderFee, pageNum, pageSize) {

    var sql = '';
    if(bForCnt) {
        sql += 'SELECT count(*) AS count';
    } else {
        sql += ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, t.c_code, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type ';
    }

    sql += ' FROM itb_coupon t, tg_type p '+
        ' WHERE t.user_id='+userId+
        ' AND t.del_flg=0 '+
        ' AND p.del_flg=0 AND p.type_id=t.limit_type ';
    if(valid==1) {
        sql += ' AND (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) AND t.state=20 ';
        if(Number(orderFee||'0')>0) {
            sql += ' AND (t.type!=20 OR (t.type=20 AND t.min_order_fee<='+orderFee+'))';
            sql += ' AND p.top_parent = 4 ';
        }
    } else {
        sql += ' AND ( sysdate()>t.expiry_time OR t.state IN (30,40) ';
        if(Number(orderFee||'0')>0) {
            sql += ' OR (t.state=20 AND t.type=20 AND t.min_order_fee>'+orderFee+')';
            sql += ' OR p.top_parent != 4 ';
        }
        sql += ' ) ';
    }
    if(!bForCnt) {
        sql += ' ORDER BY t.expiry_time ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
    }
    return sql;
}

// 根据用户id搜索优惠券信息
// 参数: userId
// 参数: valid 是否有效
// 参数: orderFee 订单金额(大于0时有效,用于判断满减券)
ItbCouponModel.prototype.getOrderCouponInfo = function(userId, valid, orderFee, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUser Err:'+err);
        }
        var  sql = sql_getOrderCouponInfo(false, userId, valid, orderFee, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};
// todo 获取符合订单条件的订单个数 begin
ItbCouponModel.prototype.getOrderCouponInfoCnt = function(userId, valid, orderFee, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUserCnt Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getOrderCouponInfo(true, userId, valid, orderFee, null, null);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};


/**
 * 查看领券用户的 sql
 * @param bForCnt: true [仅用于取count]; false [取数据]
 * @param userId: 用户id
 * @param pageNum: 取数据时分页页码
 * @param pageSize: 取数据时, 分页大小
 * @return 对应的 sql
 *
 * add: 2017-03-10 by: wangJun
 */
function sql_getPartnerCouponUsers(bForCnt, userId, pageNum, pageSize) {

    var sql = '';
    if(bForCnt) {
        sql += 'SELECT count(*) AS count';
    } else {

        sql +=
            ' SELECT DATE_FORMAT(m.draw_time, "%Y-%m-%d") AS `time`, m.`mobile`, m.couponState '+
            ' 	, CASE m.orderCount WHEN 0 THEN 0 ELSE 1 END AS orderState ' +
            '   , CASE m.custCount WHEN 0 THEN 0 ELSE 1 END AS custState ';

    }

    sql +=
        '   FROM (SELECT c.`draw_time`, c.`mobile`, c.`state`, c.`user_id`, 1 AS couponState '+
        ' 		, ( '+
        ' 			SELECT COUNT(*) '+
        ' 			FROM itb_trade_order t '+
        ' 			WHERE t.user_id = c.`user_id` '+
        ' 				AND t.state = 40 '+
        ' 				AND t.del_flg = 0 '+
        ' 				AND t.create_time > c.draw_time '+ // add by jackliu,201706052125 use draw_time
        ' 		) AS orderCount '+
        ' 		, ( '+
        ' 			select count(*) from `itb_partner_cust` pc,  `itb_partner` p '+
        ' 			where pc.`user_id`=c.`user_id` '+
        ' 			and pc.`del_flg`=0 '+
        ' 			and pc.`partner_id`=p.`partner_id` '+
        ' 			and p.`del_flg`=0 '+
        ' 			and p.`user_id` = '+userId+
        ' 		) as custCount '+
        ' 	FROM `itb_coupon` c '+
        ' 	WHERE c.`channel_u` = '+userId+
        ' 	AND c.del_flg = 0 '+
        ' 	GROUP BY c.mobile '+
        ' 	ORDER BY c.draw_time DESC '+
        ' 	) m ';
    if(!bForCnt) {
        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
    }
    return sql;
}
// 查看领券用户的 sql
// 参数: userId 兑换码
// 参数: pageNum 是否有效
// 参数: pageSize 订单金额(大于0时有效,用于判断满减券)
ItbCouponModel.prototype.getPartnerCouponUsers_v5_4 = function(userId, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUser Err:'+err);
        }

        var  sql = sql_getPartnerCouponUsers(false, userId,pageNum, pageSize);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};
// todo 获取符合订单条件的订单个数 begin
ItbCouponModel.prototype.getPartnerCouponUsersCnt_v5_4 = function(userId, pageNum, pageSize, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getCouponInfoByUserCnt Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getPartnerCouponUsers(true, userId,null, null);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};


/**
 * 查看领券用户的 sql
 * @param bForCnt: true [仅用于取count]; false [取数据]
 * @param userId: 用户id
 * @param startDt: 检索［完成时间］在该日期之后的订单 'YYYY-MM-DD'
 * @param endDt: 检索［完成时间］在该日期之前的订单 'YYYY-MM-DD'
 * @param pageNum: 取数据时分页页码
 * @param pageSize: 取数据时, 分页大小
 * @return 对应的 sql
 *
 * add: 2017-03-10 by: wangJun
 */
function sql_getIncomeOrderList(bForCnt, userId, startDt, endDt, pageNum, pageSize) {

    var sql = '';
    if(bForCnt) {
        sql += 'SELECT count(*) AS count';
    } else {
        sql +=
            ' SELECT o.order_id as orderId,o.total_fee-sum(d.mail_fee) as orderAmount,u.mobile as mobile '+
            ' ,func_calc_partner_income(o.order_id, 10) AS orderIncome '+
            ' ,DATE_FORMAT(o.finish_time, "%Y-%m-%d") AS time ';
    }
    sql += ' FROM itb_partner_cust t, '+
    ' itb_trade_order o, '+
    ' itb_user u, '+
    ' itb_partner p, '+
    ' itb_trade_order_detail d '+
    ' WHERE t.del_flg=0 '+
    ' AND t.partner_id=p.partner_id '+
    ' AND o.del_flg=0 AND o.state=40 AND o.type=0 '+// 完成的普通订单
    ' AND o.user_id=t.user_id '+
    ' AND o.finish_time is not null '+// 订单完成时间不为空
    ' AND p.del_flg=0 '+
    ' AND p.user_id= '+userId+
    ' AND u.del_flg=0 '+
    ' AND u.user_id=t.user_id '+
    ' AND t.start_time<=o.create_time '+// 下线有效期内的订单
    ' AND t.end_time>=o.create_time '+// 下线有效期内的订单
    ' AND d.del_flg=0  AND d.order_id=o.order_id ';
    if(startDt && startDt.length>0) {
        sql += ' AND o.finish_time>='+'"'+startDt+' 00:00:00"';
    }
    if(endDt && endDt.length>0) {
        sql += ' AND o.finish_time<='+'"'+endDt+' 23:59:59"';
    }
    sql += ' GROUP BY d.order_id ';

    if(!bForCnt) {
        sql += ' order by o.finish_time desc ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
    }
    return sql;
}
// 查看领券用户的 sql
// 参数: userId 兑换码
// 参数: startDt: 检索［完成时间］在该日期之后的订单 'YYYY-MM-DD'
// 参数: endDt: 检索［完成时间］在该日期之前的订单 'YYYY-MM-DD'
// 参数: pageNum 是否有效
// 参数: pageSize 订单金额(大于0时有效,用于判断满减券)
ItbCouponModel.prototype.getIncomeOrderList_v5_4 = function(userId, startDt, endDt, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getIncomeOrderList_v5_4 Err:'+err);
        }

        var  sql = sql_getIncomeOrderList(false, userId, startDt, endDt, pageNum, pageSize);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};
// todo 获取符合订单条件的订单个数 begin
ItbCouponModel.prototype.getIncomeOrderListCnt_v5_4 = function(userId, pageNum, pageSize, callback){

    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getIncomeOrderListCnt_v5_4 Err:'+err);
            return callback(err, null);
        }
        var  sql = sql_getIncomeOrderList(true, userId, null, null, null, null);
        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};


// 根据领券(state=15,expiry_time大于当前时间，draw_time时间最早)情况获取发放者id
// 参数: mobile 领券者的手机号
// 参数: callback, 参数如下
//              error
//              channel_u:发放者id
ItbCouponModel.prototype.getFirstChannelUserId = function(mobile, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.getFirstChannelUserId Err:'+err);
        }
        // Use the connection
        var sql =
            ' select * from itb_coupon c '+
            ' where c.mobile= '+ mobile +
            ' and c.del_flg=0 '+
            ' and c.state=15 '+
            ' and c.`expiry_time`>sysdate() '+
            ' order by c.`draw_time` desc '+
            ' limit 0,1 ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result && result.length) {
                return callback(error, result[0].channel_u);
            }
            return callback(error, null);
        });
    });
};


// 将用户领取(state=15)的优惠券进行兑换（同时处理多张）
// 参数: mobile 领券者的手机号
// 参数: callback, 参数如下
//              error
ItbCouponModel.prototype.exchgCouponOfuser = function(mobile, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.exchgCouponOfuser Err:'+err);
        }
        // Use the connection
        var sql =
            ' UPDATE itb_coupon t ,itb_user u '+
            ' SET t.update_time=sysdate()  '+
            ' ,t.exchg_time=sysdate()  '+
            ' ,t.user_id= u.user_id '+
            ' ,t.state=20  '+
            ' WHERE u.mobile= '+mobile+
            ' AND u.del_flg=0 '+
            ' AND t.mobile= '+mobile+
            ' AND t.state IN (10,15) '+
            ' AND t.del_flg=0 ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error) {
                return callback(error, null);
            }

            return callback(null, null);
        });
    });
};
//----------------------------------------------------
/**
 * 兑换抽奖抽到的优惠券
 * @param userId 用户唯一标识
 * @param mobile 用户手机号
 * @param cId 优惠券id
 * @param cCd 优惠券码
 * @param callback 回调
 *
 * 注意: cId/cCd: 优惠券id/优惠券码(至少一个不为空)
 */
ItbCouponModel.prototype.exchgDrawCoupon = function(userId, mobile, cId, cCd, callback) {
    var THIS = this;

    if(!cId && !cCd) {
        return callback('参数错误',null);
    }

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE itb_coupon t "+
            " SET t.update_time=sysdate() "+
            "     ,t.exchg_time=sysdate() "+
            "     ,t.draw_time=sysdate() "+
            "     ,t.user_id= "+userId;
        if(mobile) {
            sql += "     ,t.mobile=" + mobile;
        }
            sql +="     ,t.state=20 "+// 绑定到用户
            " WHERE t.del_flg=0 AND t.state IN (10,15) ";

        if(cId && Number(cId||'0')>0) {
            sql += " AND t.c_id= "+cId;
        }
        if(cCd && cCd.length>0) {
            sql += " AND t.c_code= "+cCd;
        }

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[ItbCouponModel.js] [exchgCoupon] Upd Err: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

/**
 * 获取用户所有优惠券的 sql
 * @param bForCnt true/false 是否用于获取个数
 * @param userId 用户唯一标识
 * @param pageNum 页码
 * @param pageSize 页容量
 *
 * @return sql 对应的 sql
 *
 * */
function sql_getUserAllCoupon(bForCnt, userId, pageNum, pageSize) {
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT count(*) AS count ';
    } else {
        sql += ' SELECT t.c_id, t.owner, t.lot_no, t.type, t.limit_type, t.discount, t.min_order_fee, '+
            ' t.mobile, t.user_id, t.trade_type, t.trade_obj_id, t.state, t.desc, t.c_code, '+
            ' DATE_FORMAT(t.issue_time, "%Y-%m-%d") AS issue_time, '+
            ' ifnull(DATE_FORMAT(t.expiry_time, "%Y-%m-%d"),"永久有效") AS expiry_time, '+
            ' DATE_FORMAT(t.used_time, "%Y-%m-%d %H:%i:%S") AS used_time, '+
            ' case t.type when 10 then "现金券" when 20 then "满减券" when 30 then "折扣券" end AS type_name, '+
            ' case when sysdate()>t.expiry_time then 1 else 0 end AS expiry_flg, '+
            ' ifnull(p.top_parent,0) AS top_limit_type ';
    }

    sql += ' FROM itb_coupon t, tg_type p '+
        ' WHERE t.user_id='+userId+
        ' AND t.del_flg=0 '+
        ' AND p.type_id=t.limit_type AND p.del_flg=0 ';
    //if(valid) {
    //    // TODO 暂定vip充值优惠券只有［抵扣券］，未考虑［满减券］
    //    sql += ' AND p.top_parent = 9 '+// 虚拟商品就用
    //        ' AND (sysdate()<=t.expiry_time OR t.expiry_time IS NULL) '+
    //        ' AND t.state=20 ';
    //} else {
    //    sql += ' AND ( sysdate()>t.expiry_time OR t.state IN (30,40,50) OR p.top_parent!=9 )'
    //}

    if(!bForCnt) {
        sql += ' ORDER BY t.expiry_time ';

        var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }
    }

    return sql;
}

/**
 * 获取用户所有的 vip 优惠券
 *
 * @param userId 用户唯一标识,
 * @param pageNum 页码
 * @param pageSize 页容量
 * @param callback 回调
 * */
ItbCouponModel.prototype.getUserAllCoupon = function(userId, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.exchgCouponOfuser Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = sql_getUserAllCoupon(false, userId, pageNum, pageSize);

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};

/**
 * 获取用户所有的 vip 优惠券
 *
 * @param userId 用户唯一标识,
 * @param callback 回调
 * */
ItbCouponModel.prototype.getUserAllCouponCnt = function(userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbCouponModel.exchgCouponOfuser Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = sql_getUserAllCoupon(true, userId);

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            if(error) {
                return callback(error, null);
            }
            if(result && result.length) {
                return callback(error, result[0].count);
            }
            return callback(error, null);
        });
    });
};

// 导出
module.exports = ItbCouponModel;